﻿<%@ WebHandler Language="C#" Class="RbStepCycleInfoQueryReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;
using System.Collections;
using System.Linq;

/**
 * Description: 热表工步期量信息查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2024.7.11
 **/

public class RbStepCycleInfoQueryReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    case "saveImportData":
                        r = saveImportData(jObject);//保存导入
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getQuerySql(JObject jObject)
    {
        string factoryid = "", workcenterid = "", teamid = "";
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 3)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
            teamid = selectOrg[2].ToString();
        }
        else if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
        }
        else if (selectOrg.Count == 1)
        {
            factoryid = selectOrg[0].ToString();
        }

        string sql = @"
SELECT si.stepinfoid,
       pb.productname || ':' || p.productrevision productinfo,
       wfb.workflowname || ':' || te.workflowrevision workflowinfo,
       ws.workflowstepname,si.stepno,si.description stepdesc,
       ws.makeempcounts,w.workcentername,t.teamname,
       si.preparecycle,si.makecycle,si.transfercycle,si.shipcycle,si.chargingqty,
       listagg(rdd.resourcename,'、') WITHIN GROUP (ORDER BY rdd.resourcename) stepresourcelist,
       si.revision steprev,si.steptype,
       ct.lastchangedate
FROM product p
LEFT JOIN productbase pb ON p.productbaseid = pb.productbaseid
--查询件号的工艺
LEFT JOIN workflowbase wfb1 ON wfb1.workflowbaseid = p.workflowbaseid
LEFT JOIN workflow wf1 ON wf1.workflowid = nvl(wfb1.revofrcdid, p.workflowid)
LEFT JOIN workflowbase wfb2 ON wfb2.workflowbaseid = wf1.workflowbaseid
LEFT JOIN (
    SELECT wf.workflowid,wf.workflowbaseid,wf.workflowrevision,
           CASE WHEN row_number() OVER(PARTITION BY wf.workflowbaseid ORDER BY wf.workflowid DESC) = 1 THEN '1' ELSE '0' END islatest
    FROM workflow wf
    LEFT JOIN workflowbase wfb ON wf.workflowbaseid = wfb.workflowbaseid
    WHERE wf.factoryid = '{0}'
) te ON te.workflowbaseid = wfb2.workflowbaseid

LEFT JOIN workflow wf ON wf.workflowid = te.workflowid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid

LEFT JOIN factory f ON f.factoryid = wf.factoryid
LEFT JOIN Workflowstep ws ON ws.workflowid = wf.workflowid
LEFT JOIN workcenter w ON w.workcenterid = ws.workcenterid
LEFT JOIN team t ON t.teamid = ws.teamid
LEFT JOIN Changestatus ct ON ct.parentid = wf.workflowid
INNER JOIN stepinfo si ON si.workflowstepid = ws.workflowstepid
LEFT JOIN stepinfobase sib ON si.stepinfobaseid = sib.stepinfobaseid
LEFT JOIN stepresourceinfo sri ON sri.stepid = si.stepinfoid
LEFT JOIN resourcedef rdd ON rdd.resourceid = sri.resourceid
WHERE 1=1";

        if (factoryid != "")
        {
            sql += string.Format(" AND wf.factoryid = '{0}'", factoryid);
        }

        if (workcenterid != "")
        {
            sql += string.Format(" AND ws.workcenterid = '{0}'", workcenterid);
        }

        if (teamid != "")
        {
            sql += string.Format(" AND ws.teamid = '{0}'", teamid);
        }

        // 件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND pb.productname = '{0}' ", jObject["productname"].ToString());
        }

        //工艺创建时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND ct.lastchangedate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND ct.lastchangedate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        if (jObject["selecttype"] != null)
        {
            if (jObject["selecttype"].ToString() == "最新工艺")
            {
                sql += " AND te.islatest = 1";
            }
            else
            {
                //默认工艺
                sql += " AND NVL(wfb1.revofrcdid, p.workflowid) = te.workflowid";
            }
        }

        sql += @"
GROUP BY pb.productname,p.productrevision,wfb.workflowname,wf.workflowrevision,ws.workflowstepname,
         ws.makeempcounts, w.workcentername,t.teamname,ct.lastchangedate,
         si.preparecycle,si.makecycle,si.transfercycle,si.shipcycle,si.chargingqty,
         si.stepinfoid,si.stepno,si.description,si.revision,si.steptype,te.workflowrevision
ORDER BY pb.productname,p.productrevision,si.stepinfoid";

        sql = string.Format(sql, jObject["factoryid"].ToString());
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result saveImportData(JObject jObject)
    {
        DataTable dtPostData = JsonConvert.DeserializeObject<DataTable>(jObject["willBeSavedData"].ToString());
        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtPostData.Rows)
        {
            string stepinfoid = dr["stepinfoid"].ToString();
            string preparecycle = dr["preparecycle"].ToString();
            string makecycle = dr["makecycle"].ToString();
            string transfercycle = dr["transfercycle"].ToString();
            string shipcycle = dr["shipcycle"].ToString();
            string chargingqty = dr["chargingqty"].ToString();

            string updateSql = @"UPDATE stepinfo t 
                                 SET t.preparecycle = '{0}',t.makecycle = '{1}',t.transfercycle = '{2}',t.shipcycle = '{3}',t.chargingqty = '{4}' 
                                 WHERE t.stepinfoid = '{5}'";

            updateSql = string.Format(updateSql, preparecycle, makecycle, transfercycle, shipcycle, chargingqty, stepinfoid);
            //1.保存工步排产参数
            sqlList.Add(updateSql);

            //2.每次都先清空工步设备信息
            string delSql = string.Format("DELETE stepresourceinfo t WHERE t.stepid = '{0}'", dr["stepinfoid"].ToString());
            sqlList.Add(delSql);

            string stepresourcelist = dr["stepresourcelist"].ToString();
            if (stepresourcelist != "")
            {
                if (stepresourcelist.Contains("、"))
                {
                    string[] uniqueArray = stepresourcelist.Split('、').Distinct().ToArray();
                    for (int i = 0; i < uniqueArray.Length; i++)
                    {
                        string sql = string.Format("SELECT * FROM resourcedef rd WHERE rd.resourcename = '{0}'", uniqueArray[i]);
                        DataTable dt = dal.GetDataTable(sql);
                        if (dt.Rows.Count > 0)
                        {
                            string insertSql = "INSERT INTO stepresourceinfo(resourceid,stepid,stepresourceinfoid) VALUES('{0}','{1}','{2}')";
                            insertSql = string.Format(insertSql, dt.Rows[0]["resourceid"].ToString(), stepinfoid, Guid.NewGuid().ToString());
                            sqlList.Add(insertSql);
                        }
                    }
                }
                else
                {
                    string sql = string.Format("SELECT * FROM resourcedef rd WHERE rd.resourcename = '{0}'", stepresourcelist);
                    DataTable dt = dal.GetDataTable(sql);
                    if (dt.Rows.Count > 0)
                    {
                        string insertSql = "INSERT INTO stepresourceinfo(resourceid,stepid,stepresourceinfoid) VALUES('{0}','{1}','{2}')";
                        insertSql = string.Format(insertSql, dt.Rows[0]["resourceid"].ToString(), stepinfoid, Guid.NewGuid().ToString());
                        sqlList.Add(insertSql);
                    }
                }
            }
            dal.ExecuteSqlTran(sqlList);
        }
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(dtPostData.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dt.Columns["stepinfoid"].Caption = "工步id";
        dt.Columns["productinfo"].Caption = "零件信息";
        dt.Columns["workflowinfo"].Caption = "工艺信息";
        dt.Columns["workflowstepname"].Caption = "工序";
        dt.Columns["makeempcounts"].Caption = "作业人数";
        dt.Columns["workcentername"].Caption = "工区";
        dt.Columns["teamname"].Caption = "班组";

        dt.Columns["preparecycle"].Caption = "准备工时";
        dt.Columns["makecycle"].Caption = "加工工时";
        dt.Columns["transfercycle"].Caption = "转运工时";
        dt.Columns["shipcycle"].Caption = "转工周期";
        dt.Columns["chargingqty"].Caption = "装炉/槽量";
        dt.Columns["stepresourcelist"].Caption = "设备";

        dt.Columns["lastchangedate"].Caption = "工艺创建时间";
        dt.Columns["stepno"].Caption = "工步号";
        dt.Columns["stepdesc"].Caption = "工步名称";
        dt.Columns["steprev"].Caption = "工步版本";
        dt.Columns["steptype"].Caption = "工步类型";

        ExportHelper.ExportExcel(dt, "热表工步期量信息查询报表");
    }
}